Combining all things shown so far, as well as showing further features, let us look at a more complicated, practical example, and how the Database Wizard makes this possible:

Say we have two databases – in one is our standard employee / customer / order database, in a second one we have a large set of customer information combined with regional data such as city population. Our goal is to create a report of information resulting from cross-checking order information with our customer information on our second database – say, a list of all orders made by customers living in a city of a population of 10,000 or less, including the employees responsible for the orders, and the product name sold in the order.

Our first step is to set up our order database as our first database connection. We then add our second database connection by double clicking on "New Connection...", and entering the data for this second database. In the database browser tree, we now have two database connections to choose our table sources from (see image 1).

By either double clicking, or by highlighting and then clicking on Add, we select the table sources "Orders", "Order Details", "Employees", "Products", and "Customers" from our first database.

We now join the following columns:

• Orders with Order_Details on OrderID
• Orders with Employees on EmployeeID
• Products with Order_Details on ProductID
• Customers with Orders on CustomerID

As you can see, this leads to quite a web of tables. However, there is a function in Database Wizard to simplify this – since we only will need a small part of the columns of these tables, we can convert these table sources to a single SQL command called "OrderInfo". To do this, simply select the node "Northwind" and click on "To SQL...".

This will open an SQL Editor dialog and will fill it with the current SQL statement – if we are not using any fields in our report yet, this box will be empty, since no SQL statement is as of yet being sent to the database.

All we need to add or edit is which columns to select, so we insert the columns "LastName" from Employees, "OrderID" from Orders, "ProductName" from Products, "CustomerID" as well as "ContactName" from Customers into the SQL statement, and specify the joins we wish to use (see image 3). After clicking on OK, this will convert all our current table sources into a single table source, making it much easier to work.

Now we still need the tables from our second database connection, "CustList" and "Cities". We join:

• CustList with OrderInfo on CustomerID
• CustList with Cities on CityName

We now have completed our goal – we are supplying our report with the data we need to create the report we want, from two databases - all in about 4 minutes!

There are two ways to accomplish this.

One is by specifying a "Record Selection Formula" in Crystal or Basic syntax by choosing the menu point "Record Selection Filter..." under the "Report" menu. An example would be that you want to filter out all Employees whose salary is below $60,000 – simply specify "{Employees.Salary} >= 60000" as your record selection formula.

The other solution would be to create an SQL command and include a specific WHERE clause in your SQL statement, such as ...WHERE Employees.Salary >= 60000.

This error message means that somewhere in your report, you are using a field of this table. This makes it impossible to delete the used table, because it would render your report useless – the data your report is relying on would be gone.

If you really want to delete this table source, you will first have to remove all fields referring to the table source from your report.

For example, say you want to remove the table "Employees", but you are referring to "EmployeeID" and "LastName" in one of your formulas. Simply either change your formula to no longer refer to these fields, or delete your formula.

When a report is first opened, the connection is not opened until needed. This allows you to make changes to your report without having to connect to your database first, a process which can take some time, depending on the database.

If you would like to connect to your database, simply right-click on the gray database connection, and select the option "Activate Connection" – this will cause i-net Designer to connect to your database and show its available database fields.

To do this, simply right-click on the command whose SQL you wish to modify, and select "Edit" – this will open up the "SQL Editor" with the commands current SQL and will allow you to edit it.

Each table source has the option "Set Location" in its context menu, which can be reached by simply right-clicking on the table source.

Note that the new alias must only contain letters, numbers, spaces, or underscores, and also must begin with a letter – that means 1Cus is not allowed, nor is Cus$.